package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import util.JDBCUtil;
import domain.CrewJob;
import ex.DatabaseException;

public class CrewJobDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String INSERT_CREWJOB="insert into crew_job (uid, mid, crewtitle," +
			"description,  count) values (?, ?, ?, ?, ?) ";
	private static final String SELECT_CREWJOB_BY_MID="select * from crew_job where mid= ? ";
	private static final String DELETE_CREWJOB="delete from crew_job where cjid = ?";
	private static final String EDIT_CREWJOB="update crew_job set crewtitle = ?, description = ?,timestamp = ?, count = ? where cjid = ?";
	
	//Insertion of a new CrewJob to the database
	public void insertCrewJob(CrewJob crewJob) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(INSERT_CREWJOB);
			stmt.setLong(1, crewJob.getUid());
			stmt.setLong(2, crewJob.getMid());
			stmt.setLong(3, crewJob.getCrewtitle());
			stmt.setString(4, crewJob.getDescription());
			stmt.setInt(5, crewJob.getCount());
			
			stmt.execute();			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with inserting crew job information! "	+ e.getMessage());
		}
	}

	//Selection of the CrewJob Information using the Movie ID in the CrewJob table
	public LinkedList<CrewJob> selectCrewJobByMid(long mid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		
		LinkedList<CrewJob> crewJobs = new LinkedList<CrewJob>();
		
		try{
			stmt = conn.prepareStatement(SELECT_CREWJOB_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				long uid = rs.getLong("uid");
				long cjid = rs.getLong("cjid");
				long crewtitle = rs.getLong("crewtitle");
				String description = rs.getString("description");
				Timestamp timestamp = rs.getTimestamp("timestamp");
				int count = rs.getInt("count");
				crewJobs.add(new CrewJob(cjid,uid, mid, crewtitle, description, timestamp, count));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);		
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving crew job information! "+ e.getMessage());
		}	
		return crewJobs;		
	}

	//Removal of a CrewJob from the database
	public void deleteCrewJob(CrewJob crewJob) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(DELETE_CREWJOB);
			stmt.setLong(1, crewJob.getCjid());
			rs = stmt.executeQuery();
			stmt.execute();		
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with deleting crew job information! "	+ e.getMessage());
		}
			
	}

	//Editing the CrewJob fields
	public void editCrewJob(CrewJob crewJob) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		
		try {
			stmt = conn.prepareStatement(EDIT_CREWJOB);
			stmt.setLong(1, crewJob.getCrewtitle());
			stmt.setString(2, crewJob.getDescription());
			stmt.setTimestamp(3, crewJob.getTimestamp());	
			stmt.setInt(4, crewJob.getCount());
			stmt.setLong(5, crewJob.getCjid());

			stmt.execute();			
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with editing crew job information! "	+ e.getMessage());
		}
	}
}
